﻿using System;
using System.Collections.Generic;
using System.Linq;
using Common.Extension;
using Common.Model;
using Common.util;
using Dapper;

namespace Common.Dao
{
    public class NewsDao
    {
        /// <summary>
        /// 保存新闻
        /// </summary>
        /// <param name="news"></param>
        public static void BatchSaveNews(List<News> news)
        {
            String sql =
                @"insert ignore `news`(classify_result,keywords,block_classify,title,`from`,content,content_link,site_time,stocks,content_id,abstract,tag,crawling_time) " +
                "VALUES (@ClassifyResult,@Keywords,@BlockClassify,@Title,@From,@Content,@ContentLink,@SiteTime,@Stocks,@ContentId,@Abs,@Tag,@CrawlingTime)";
            using (var conn = DbUtils.GetHotTopicConnection())
            {
                var execute = conn.InsertMultiple(sql, news);

            }
        }

        /// <summary>
        /// 保存IsTraingSet字段
        /// </summary>
        /// <param name="news"></param>
        public static void BatchUpdateIsTrainingSet(List<News> news)
        {
            String sql =
                @"UPDATE `news` SET is_training_set = @IsTrainingSet WHERE content_id = @ContentId";
            using (var conn = DbUtils.GetHotTopicConnection())
            {
                conn.InsertMultiple(sql, news);
            }
        }

        /// <summary>
        /// 保存新闻关键词
        /// </summary>
        /// <param name="news"></param>
        public static void UpdateKeywords(List<News> news)
        {
            String sql =
                @"UPDATE `news` SET keywords = @KeyWords WHERE content_id = @ContentId";
            using (var conn = DbUtils.GetHotTopicConnection())
            {
                conn.InsertMultiple(sql, news);
            }
        }
        
        /// <summary>
        /// 保存新闻版块分类
        /// </summary>
        /// <param name="news"></param>
        public static void UpdateClassifyResult(List<News> news)
        {
            String sql =
                @"UPDATE `news` SET classify_result = @ClassifyResult WHERE content_id = @ContentId";
            using (var conn = DbUtils.GetHotTopicConnection())
            {
                conn.InsertMultiple(sql, news);
            }
        }

        /// <summary>
        /// 获取最新一条新闻
        /// </summary>
        /// <param name="from"></param>
        /// <returns></returns>
        public static News GetLastNews(String from)
        {
            String sql =
                "SELECT classify_result AS ClassifyResult,keywords,content_id AS ContentId,title,`from`,abstract AS abs,stocks,block_classify AS BlockClassify,site_time AS SiteTime,content_link AS ContentLink,crawling_time As CrawlingTime,is_training_set AS IsTrainingSet " +
                "FROM news " +
                "WHERE `from` = @from " +
                "ORDER BY site_time DESC " +
                "LIMIT 1";

            using (var conn = DbUtils.GetHotTopicConnection())
            {
                List<News> newsList = conn.Query<News>(sql, new {from = from})?.ToList();
                if (newsList != null && newsList.Any())
                {
                    return newsList.First();
                }
                else
                {
                    return null;
                }
            }
        }
        
        /// <summary>
        /// 获取最新100条新闻
        /// </summary>
        /// <returns></returns>
        public static List<News> GetLastNews()
        {
            String sql =
                "select classify_result AS ClassifyResult,keywords,content_id AS ContentId,title,`from`,abstract AS abs,stocks,block_classify AS BlockClassify,site_time AS SiteTime,content_link AS ContentLink,crawling_time As CrawlingTime,is_training_set AS IsTrainingSet " +
                "FROM news ORDER BY site_time DESC " +
                "LIMIT 100";

            using (var conn = DbUtils.GetHotTopicConnection())
            {
                return conn.Query<News>(sql)?.ToList();
            }
        }
        
        /// <summary>
        /// 获取未分类的新闻
        /// </summary>
        /// <returns></returns>
        public static List<News> GetNewsWhereBlockClassifyIsNull()
        {
            String sql =
                "SELECT classify_result AS ClassifyResult,content_id AS ContentId,title,keywords,`from`,abstract AS abs,stocks,block_classify AS BlockClassify FROM news WHERE block_classify is NULL";

            using (var conn = DbUtils.GetHotTopicConnection())
            {
                return conn.Query<News>(sql)?.ToList();
            }
        }
        
        /// <summary>
        /// 获取未生成关键词的新闻
        /// </summary>
        /// <returns></returns>
        public static List<News> GetNewsWhereKeywordsIsNull()
        {
            String sql =
                "SELECT classify_result AS ClassifyResult,content_id AS ContentId,title,keywords,`from`,abstract AS abs,stocks,block_classify AS BlockClassify FROM news WHERE keywords is NULL";

            using (var conn = DbUtils.GetHotTopicConnection())
            {
                return conn.Query<News>(sql)?.ToList();
            }
        }


        /// <summary>
        /// 获取全部新闻
        /// </summary>
        /// <returns></returns>
        public static List<News> GetAllNews()
        {
            String sql =
                "SELECT classify_result AS ClassifyResult,keywords,content_id AS ContentId,title,`from`,abstract AS abs,stocks,block_classify AS BlockClassify,site_time AS SiteTime,content_link AS ContentLink,crawling_time As CrawlingTime,is_training_set AS IsTrainingSet FROM `news`";

            using (var conn = DbUtils.GetHotTopicConnection())
            {
                return conn.Query<News>(sql)?.ToList();
            }
        }

        /// <summary>
        /// 获取可作为训练数据的新闻
        /// </summary>
        /// <returns></returns>
        public static List<News> GetTrainingNews()
        {
            String sql =
                "SELECT classify_result AS ClassifyResult,keywords,crawling_time As CrawlingTime,site_time AS SiteTime,content_id AS ContentId,title,`from`,abstract AS abs,stocks,block_classify AS BlockClassify FROM news WHERE is_training_set = 1";

            using (var conn = DbUtils.GetHotTopicConnection())
            {
                return conn.Query<News>(sql)?.ToList();
            }
        }
        
        /// <summary>
        /// 获取新闻分类好的新闻
        /// </summary>
        /// <returns></returns>
        public static List<News> GetNewsWhereBlockClassifyNotNull()
        {
            String sql =
                "SELECT classify_result AS ClassifyResult,keywords,content_id AS ContentId,title,`from`,abstract AS abs,stocks,block_classify AS BlockClassify FROM news WHERE block_classify IS NOT NULL";

            using (var conn = DbUtils.GetHotTopicConnection())
            {
                return conn.Query<News>(sql)?.ToList();
            }
        }
        
                
        /// <summary>
        /// 获取关键词不为null的新闻列表
        /// </summary>
        /// <returns></returns>
        public static List<News> GetNewsWhereKeywordsNotNull()
        {
            String sql =
                "SELECT classify_result AS ClassifyResult,keywords,content_id AS ContentId,title,`from`,abstract AS abs,stocks,block_classify AS BlockClassify FROM news WHERE keywords IS NOT NULL";

            using (var conn = DbUtils.GetHotTopicConnection())
            {
                return conn.Query<News>(sql)?.ToList();
            }
        }
        
        /// <summary>
        /// 更新新闻分类
        /// </summary>
        /// <param name="news"></param>
        public static void UpdateBlockClassify(List<News> news)
        {
            String sql =
                @"UPDATE `news` SET block_classify = @BlockClassify WHERE content_id = @ContentId";
            using (var conn = DbUtils.GetHotTopicConnection())
            {
                conn.InsertMultiple(sql, news);
            }
        }
    }
}